from pymysql import Connection

try:
    # 连接到数据库，直接在构造函数中指定数据库名
    conn = Connection(
        host='10.13.10.53',
        port=3306,
        user='tester',
        password='AY^1F6pZlkVx9jqd',
        database='open_employ',  # 直接指定数据库名
        autocommit=True
    )
    # 创建游标
    cursor = conn.cursor()

    # 数据库插入数据时，如果有字段是动态值，在value中该字符赋值时，先使用占位符占用
    # 执行sql时，在参数中传入动态值


# ---------------------- 插入打卡数据
    # name = '杨培培'
    # worker_no = 80002058
    # name = '欧阳娜娜'
    # worker_no = 80000116
    # name = '摩卡猫猫'
    # worker_no = 80001799
    # name = '黄燕婷'
    # worker_no = 80053314
    # clock_time = '2025-03-14 02:59:59'
    # attend_type = 0    # 0-上班打卡 1-下班打卡
    # 插入语句 当value的值是动态时，可以使用占位符进行占位
    # insert_sql = "INSERT INTO `attend_machine_record` (`name`, `worker_no`, `clock_type`, `clock_time`, `is_deleted`, `gmt_create`, `gmt_modified`, `attend_machine_id`, `attend_machine_name`, `attend_type`, `valid_status`, `update_by`) " \
    #              "VALUES(%s, %s, 1, %s, 0, %s, %s, 2107839, '三楼前台', %s, 1, NULL )"
    # # 执行插入操作 执行insert语句，依次对语句中的占位符赋值
    # rows = cursor.execute(insert_sql, (name, worker_no, clock_time, clock_time, clock_time, attend_type))

# ------------------    插入企微外出记录
    # name = '欧阳娜娜'
    # worker_no = 80000116
    # out_start_time = '2025-03-16 09:00:00'
    # out_end_time = '2025-03-16 19:00:00'

    # insert_sql = "INSERT INTO `attend_qw_out_record` (`qw_sp_no`, `qw_applyer_name`, `qw_applyer_worker_no`, `qw_applyer_dept`, `out_start_time`, `out_end_time`, `out_duration`, `out_reason`, `out_location`, `qw_sp_status`, `approve_flow`, `apply_time`, `remarks`, `annex`, `is_deleted`, `gmt_create`, `gmt_modified`, `valid`, `actual_duration`, `update_by`, `over_month`, `actual_duration_hour`) " \
    #              "VALUES('202503170100', %s, %s, '轻喜到家/产品技术部/测试部', %s, %s, '11', '看哭了', '哦哦哦', 2, '已同意|赵竹 ', '2024-08-11 23:11:10', NULL, NULL, 0, '2024-08-23 18:33:45', '2024-08-28 20:28:42', 1, '0.900', NULL, 0, NULL)"
    # rows = cursor.execute(insert_sql, (name, worker_no, out_start_time, out_end_time))

# -------------------- 插入企微请假记录
#     name = '欧阳娜娜'
#     worker_no = 80000116
#     leave_start_time = '2025-03-14 09:00:00'
#     leave_end_time = '2025-03-14 19:00:00'
#     insert_sql = "INSERT INTO `attend_qw_ask_leave_record` (`qw_sp_no`, `qw_applyer_name`, `qw_applyer_worker_no`, `qw_applyer_dept`, `leave_type`, `leave_start_time`, `leave_end_time`, `leave_duration`, `leave_reason`, `qw_sp_status`, `approve_flow`, `apply_time`, `remarks`, `annex`, `is_deleted`, `gmt_create`, `gmt_modified`, `valid`, `actual_duration`, `update_by`, `over_month`, `actual_duration_hour`) " \
#                  "VALUES('202503170100', %s, %s, '轻喜到家/产品技术部/测试部', 1, %s, %s, '23', '', 2, '已同意|赵竹 ', '2024-07-31 18:05:40', NULL, NULL, 0, '2024-08-06 21:48:42', '2024-10-21 20:53:57', 1, '1.000,0.000', NULL, 1, NULL)"
#     rows = cursor.execute(insert_sql, (name, worker_no, leave_start_time, leave_end_time))

# ------------------- 插入企微出差记录
#     name = '欧阳娜娜'
#     worker_no = 80000116
#     away_start_time = '2025-03-12 09:00:00'
#     away_end_time = '2025-03-12 19:00:00'
#     insert_sql = "INSERT INTO `attend_qw_away_record` (`qw_sp_no`, `qw_applyer_name`, `qw_applyer_worker_no`, `qw_applyer_dept`, `away_start_time`, `away_end_time`, `away_duration`, `away_reason`, `away_location`, `away_vehicle`, `away_stay`, `away_stay_way`, `away_purpose`, `qw_sp_status`, `approve_flow`, `apply_time`, `remarks`, `annex`, `is_deleted`, `gmt_create`, `gmt_modified`, `valid`, `actual_duration`, `update_by`, `over_month`, `actual_duration_hour`) " \
#                  "VALUES('202503170100', %s, %s, '轻喜到家/产品技术部/测试部', %s, %s, '34', NULL, '两个十多个', NULL, 1, '无', NULL, 2, '已同意|赵竹 ', '2024-08-05 23:34:33', NULL, NULL, 0, '2024-08-06 21:48:46', '2024-10-21 19:17:43', 1, '0.636', '赵竹', 0, NULL)"
#     rows = cursor.execute(insert_sql, (name, worker_no, away_start_time, away_end_time))

# ------------------- 插入企微调休记录
#     name = '欧阳娜娜'
#     worker_no = 80000116
#     leave_start_time = '2025-03-10 09:00:00'
#     leave_end_time = '2025-03-10 19:00:00'
#     insert_sql = "INSERT INTO `attend_qw_off_leave_record` (`qw_sp_no`, `qw_applyer_name`, `qw_applyer_worker_no`, `qw_applyer_dept`, `leave_start_time`, `leave_end_time`, `leave_duration`, `leave_reason`, `leave_work_handover`, `leave_key_work`, `qw_sp_status`, `approve_flow`, `apply_time`, `remarks`, `annex`, `is_deleted`, `gmt_create`, `gmt_modified`, `dept_id`, `valid`, `actual_duration`, `update_by`, `over_month`, `actual_duration_hour`) " \
#                  "VALUES('202503170100', %s, %s, '轻喜到家/产品技术部/信息技术部/商城组', %s, %s, '3.0', '1', '1', '1', 2, '已同意|摩卡猫猫 ', '2025-03-12 17:26:58', NULL, NULL, 0, '2025-03-12 17:39:39', '2025-03-14 16:51:53', 158, 1, '0.000', NULL, 0, '0.000')"
#     rows = cursor.execute(insert_sql, (name, worker_no, leave_start_time, leave_end_time))

# ---------------
    name = '宋双双'
    worker_no = 80001847
    clock_date = '2025-03-22'
    clock_time = '2025-03-22 19:30:00'
    # checkin_type = '上班打卡'
    checkin_type = '下班打卡'
    insert_sql = "INSERT INTO `open_employ`.`attend_qw_clock_record` (`qw_sp_no`, `qw_applyer_name`, `qw_applyer_worker_no`, `qw_applyer_dept`, `clock_date`, `clock_time`, `annex`, `remarks`, `valid`, `is_deleted`, `gmt_create`, `gmt_modified`, `update_by`, `over_month`, `checkin_type`) " \
                 "VALUES (NULL, %s, %s, '轻喜到家/产品技术部/信息技术部/商城组', %s, %s, NULL, '', 1, 0, NULL, '2025-03-08 16:34:45', '郭灿镇', 0, %s)"
    rows = cursor.execute(insert_sql, (name, worker_no, clock_date, clock_time, checkin_type))


    print(f"插入 {rows} 条数据成功")

except Exception as e:
    print(f"发生错误: {e}")
finally:
    if 'conn' in locals() and conn:
        # 关闭游标和连接
        cursor.close()
        conn.close()
